package nl.janvanloenen.bkc;

import java.util.ArrayList;
import java.util.List;

import nl.janvanloenen.bkc.Match.State;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 2;

	// Database Name
	private static final String DATABASE_NAME = "teamManager";

	// Contacts table name
	private static final String TABLE_CONTACTS = "teams";

	// Contacts Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_TEAMA = "team_a";
	private static final String KEY_TEAMB = "team_b";
	private static final String KEY_DATE = "date";
	private static final String KEY_START_TIME = "start_time";
	private static final String KEY_END_TIME = "end_time";
	private static final String KEY_SCORE_A = "scoreteama";
	private static final String KEY_SCORE_B = "scoreteamb";
	private static final String KEY_STATE = "state";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_TEAMA + " TEXT,"
				+ KEY_TEAMB + " TEXT," + KEY_DATE + " TEXT," + KEY_START_TIME
				+ " INTEGER," + KEY_END_TIME + " INTEGER," + KEY_SCORE_A
				+ " INTEGER," + KEY_SCORE_B + " INTEGER," + KEY_STATE + " INTEGER" + ")";
		db.execSQL(CREATE_CONTACTS_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

		// Create tables again
		onCreate(db);
	}

	// Adding new contact
	void addMatch(Match match) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_TEAMA, match.getTeamA()); // Team A
		values.put(KEY_TEAMB, match.getTeamB()); // Team B
		values.put(KEY_DATE, match.getDate().toString()); // Date
		values.put(KEY_START_TIME, match.getStartTime()); // Start Time
		values.put(KEY_END_TIME, match.getEndTime()); // End Time
		values.put(KEY_SCORE_A, match.getScoreTeamA()); // Score Team A
		values.put(KEY_SCORE_B, match.getScoreTeamB()); // Score Team B
		values.put(KEY_STATE, match.getState().toString()); // State
		// Inserting Row
		db.insert(TABLE_CONTACTS, null, values);
		db.close(); // Closing database connection
	}

	// Getting single contact
	Match getMatch(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
				KEY_TEAMA, KEY_TEAMB, KEY_DATE, KEY_START_TIME, KEY_END_TIME, KEY_SCORE_A, KEY_SCORE_B, KEY_STATE },
				KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null,
				null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Match contact = new Match(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), cursor.getString(2), cursor.getString(3),
				cursor.getInt(4), cursor.getInt(5), cursor.getInt(6), cursor.getInt(7), cursor.getString(8));
		// return contact
		cursor.close();
		return contact;
	}

	// Getting All Contacts
	public List<Match> getAllMatches() {
		List<Match> matchList = new ArrayList<Match>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Match match = new Match();
				match.setID(Integer.parseInt(cursor.getString(0)));
				match.setTeamA(cursor.getString(1));
				match.setTeamB(cursor.getString(2));
				match.setDate(cursor.getString(3));
				match.setStartTime(cursor.getInt(4));
				match.setEndTime(cursor.getInt(5));
				match.setScoreTeamA(cursor.getInt(6));
				match.setScoreTeamB(cursor.getInt(7));
				match.setState(State.valueOf(cursor.getString(8)));
				// Adding contact to list
				matchList.add(match);
			} while (cursor.moveToNext());
		}

		// return contact list
		cursor.close();
		return matchList;
	}

	// Updating single contact
	public int updateMatch(Match match) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_TEAMA, match.getTeamA());
		values.put(KEY_TEAMB, match.getTeamB());
		values.put(KEY_DATE, match.getDate());
		values.put(KEY_START_TIME, match.getStartTime());
		values.put(KEY_END_TIME, match.getEndTime());
		values.put(KEY_SCORE_A, match.getScoreTeamA());
		values.put(KEY_SCORE_B, match.getScoreTeamB());
		values.put(KEY_STATE, match.getState().toString());

		// updating row
		return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
				new String[] { String.valueOf(match.getID()) });
	}

	// Deleting single contact
	public void deleteMatch(Match contact) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
				new String[] { String.valueOf(contact.getID()) });
		db.close();
	}
	
	// Delete all
	public void deleteDB(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_CONTACTS, null, null);
		db.close();
	}

	// Getting contacts Count
	public int getMatchCount() {
		String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int count = cursor.getCount();
		cursor.close();

		return count;
	}

}
